package zdy.web.orm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import zdy.web.bean.User;

public class DBhelp {
	private  Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://localhost:3306/test";
	    String username = "root";
	    String password = "root";
	    Connection conn = null;
	    try {
	        Class.forName(driver); //classLoader,加载对应驱动
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
	 //添加  
	public  int insert(User user) {
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "insert into user (Name,Age) values(?,?)";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(1, user.getName());
	        pstmt.setInt(2, user.getAge());
	        i = pstmt.executeUpdate();
	        System.out.println("insert: " + i);
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
    //删除  
	public  int delete(String name) {
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "delete from user where Name='" + name + "'";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("delete: " + i);
	        pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
    //更新  
    public int update(User t){  
    	    Connection conn = getConn();
    	    int i = 0;
    	    String sql = "update user set Age=" + t.getAge() + " where Name='" + t.getName() + "'";
    	    PreparedStatement pstmt;
    	    try {
    	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
    	        i = pstmt.executeUpdate();
    	        System.out.println("update : " + i);
    	        pstmt.close();
    	        conn.close();
    	    } catch (SQLException e) {
    	        e.printStackTrace();
    	    }
    	    return i;
    }  
    //根据id查询  
    public User getOne(int id){  
    	Connection conn = getConn();
        String sql = "select id,name,age from user where id=?";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();   
            User u=new User();
    
            if (rs.next()) {           
            
                u.setId(rs.getInt(1));
                u.setName(rs.getString(2));
                u.setAge(rs.getInt(3));              
            }
            System.out.println("==========getOne===begin===============");
            System.out.println("======u.id=="+u.getId()+"===========");
            System.out.println("======u.name=="+u.getName()+"===========");
            System.out.println("======u.age=="+u.getAge()+"===========");
            System.out.println("==========getOne===end===============");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }  
    //查询所有  
    public   Integer getAll() {
        Connection conn = getConn();
        String sql = "select * from user";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("=============getAll begin===============");
            System.out.println("name,	age,	group,	id");
            
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                 }
                System.out.println("");
            }
                System.out.println("============getAll end================");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
